﻿
using EFCoreLenarn;
using ExpressionTreeToString;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System.Linq.Expressions;
using System.Reflection;
using System.Text.Json.Serialization;
using System.Xml.Linq;
using static System.Linq.Expressions.Expression;


//using (var context = new TestDbContext())
{
    /*  // 插入数据
      var b1 = new Book { AuthorName = "杨中科", Title = "零基础趣学C语言", Price = 59.8, PubTime = new DateTime(2019, 3, 1) };
      var b2 = new Book { AuthorName = "Wja", IdDeleted=true, Title = "算法（第四版）", Price = 99, PubTime = new DateTime(2012, 10, 1) };
      var b3 = new Book { AuthorName = "吴军", Title = "数学之美", Price = 69, PubTime = new DateTime(2020, 5, 1) };
      var b4 = new Book { AuthorName = "杨中科", Title = "程序员的SQL金典", Price = 52, PubTime = new DateTime(2008, 9, 1) };
      var b5 = new Book { AuthorName = "吴军", Title = "文明之光", Price = 246, PubTime = new DateTime(2017, 3, 1) };
      context.Books.Add(b1);
      context.Books.Add(b2);
      context.Books.Add(b3);
      context.Books.Add(b4);
      context.Books.Add(b5);
      context.SaveChanges();*/


    /*Console.WriteLine("*******查询所以书********");
    foreach (var book in context.Books)
    {
        Console.WriteLine($"Id={book.Id},Title={book.Title},Price={book.Price}");
    }
    Console.WriteLine();
    Console.WriteLine("价格高于80元的书");
    foreach (var book in context.Books.Where(i => i.Price > 80))
    {
        Console.WriteLine($"Id={book.Id},Title={book.Title},Price={book.Price}");
    }
    Console.WriteLine();
    Console.WriteLine("分组后的结果：");
    var groups = context.Books.GroupBy(i => i.AuthorName).Select(g => new { AuthorName = g.Key, BooksCount = g.Count(), MaxPrice = g.Max(b => b.Price) });
    foreach (var group in groups)
    {
        Console.WriteLine($"Author:{group.AuthorName},BooksCount:{group.BooksCount},MaxPrice={group.MaxPrice}");
    }*/

    /*//Console.WriteLine();
    //Console.WriteLine("修改操作");
    //var b = context.Books.Single(b => b.Title == "数学之美");
    //b.AuthorName = "Wu Jun";
    //context.SaveChanges();

    //var books = context.Books.Where(b=>b.PubTime.Year>2010).Take(3);
    //foreach (var book in books)
    //{
    //    Console.WriteLine(book.Title);
    //}*/

    //var books= context.Books.Where(b=>b.Price>20).ToArray();
    //foreach (var book in books)
    //{
    //    Console.WriteLine($"Id:{book.Id},Title:{book.Title},Price:{book.Price},AuthorName:{book.AuthorName}");
    //}

    //Func<Book, bool> f1 = b => b.Price > 5 || b.AuthorName.Contains("杨中科");
    //Expression<Func<Book, bool>> e = b => b.Price > 5 || b.AuthorName.Contains("杨中科");
    ////Console.WriteLine(f1);
    ////Console.WriteLine(e);
    ////Console.WriteLine(e.ToString("Object notation","C#"));
    //Console.WriteLine(e.ToString("Factory methods","C#"));

}

//Console.WriteLine("Hello, World!");
// using static System.Linq.Expressions.Expression

/*var b = Parameter(
    typeof(Book),
    "b"
);

Lambda(
    OrElse(
        GreaterThan(
            MakeMemberAccess(b,
                typeof(Book).GetProperty("Price")
            ),
            Constant(5)
        ),
        Call(
            MakeMemberAccess(b,
                typeof(Book).GetProperty("AuthorName")
            ),
            typeof(string).GetMethod("Contains", new[] { typeof(string) }),
            Constant("杨中科")
        )
    ),
    b
);*/

//Console.WriteLine(  );
//using var mySqlCtx = new MySqlDbContext();

/*// 插入数据
var b1 = new Book { AuthorName = "杨中科", Title = "零基础趣学C语言", Price = 59.8, PubTime = new DateTime(2019, 3, 1) };
var b2 = new Book { AuthorName = "Wja", Title = "算法（第四版）", Price = 99, PubTime = new DateTime(2012, 10, 1) };
var b3 = new Book { AuthorName = "吴军", Title = "数学之美", Price = 69, PubTime = new DateTime(2020, 5, 1) };
var b4 = new Book { AuthorName = "杨中科", Title = "程序员的SQL金典", Price = 52, PubTime = new DateTime(2008, 9, 1) };
var b5 = new Book { AuthorName = "吴军", Title = "文明之光", Price = 246, PubTime = new DateTime(2017, 3, 1) };
mySqlCtx.Books.Add(b1);
mySqlCtx.Books.Add(b2);
mySqlCtx.Books.Add(b3);
mySqlCtx.Books.Add(b4);
mySqlCtx.Books.Add(b5);
mySqlCtx.SaveChanges();
*/
//var mysqlBook = mySqlCtx.Books.Where(b => b.PubTime.Year > 2010).Take(3);
//foreach (var book in mysqlBook)
//{
//    Console.WriteLine(book.Title);
//}


/*// 一对多关系
//Article article = new Article();
//article.Title = "微软发布 .NET 6 大版本首个预览版本的到来。";
//article.Content = "微软昨日在一篇官网博客中宣布了 .NET 6 首个预览版本的到来";
//Comment c1 = new Comment() { Message = "支持" };
//Comment c2 = new Comment() { Message = "微软太牛了" };
//Comment c3 = new Comment() { Message = "支持" };
//article.Comments.Add(c1);
//article.Comments.Add(c2);
//article.Comments.Add(c3);
//using TestDbContext ctx = new TestDbContext();
// 使用这种方式插入数据是可用的，
//ctx.Articles.Add(article);
//await ctx.SaveChangesAsync();*/

/*// 还可用通过以下方式插入数据
Article a1 = new Article();
a1.Title = "关于 .NET 5 正式发布，你应该了解5件事";
a1.Content = ".NET 5 是 .NET Core 3.1 和 .NET Framework 4.8 的后续产品";
Comment c1 = new Comment() { Message = "已经在用了",Article=a1};
Comment c2 = new Comment() { Message="我们公司项目已经升级到 .NET5 了",Article=a1 };
using TestDbContext ctx = new TestDbContext();
ctx.Comments.Add(c1);
ctx.Comments.Add(c2);
ctx.SaveChanges();*/

/*// 关联查询操作
using TestDbContext ctx = new TestDbContext();
Article article = ctx.Articles.Include(a => a.Comments).AsSplitQuery().Single(a => a.Id == 1);
//Article article = ctx.Articles.Single(a=>a.Id==1);
Console.WriteLine(article.Title);
foreach (var c in article.Comments)
{
    Console.WriteLine(c.Id + ":" + c.Message);
}*/

// 关联查询主键
using TestDbContext ctx = new TestDbContext();

/*SELECT[t].[Id], [t].[ArticleId], [t].[Message], [t0].[Id], [t0].[Content], [t0].[Title]
      FROM[T_Comment] AS[t]
      INNER JOIN[T_Article] AS [t0] ON[t].[ArticleId] = [t0].[Id]*/
/*foreach (var c in ctx.Comments.Include(c=>c.Article))
{
    Console.WriteLine($"{c.Id} : {c.Message} ; {c.Article.Id}" );
}
*/
/*SELECT[t].[Id], [t].[ArticleId], [t].[Message]
      FROM[T_Comment] AS[t]*/
//foreach (var c in ctx.Comments)
//{
//    Console.WriteLine($"{c.Id} : {c.Message} ; {c.ArticleId}");
//}

/*User u1 = new User() { Name = "杨中科" };
Leave leave = new Leave();
leave.Requester = u1;
leave.From = new DateTime(2021, 8, 8);
leave.To = new DateTime(2021, 8, 9);
leave.Remarks = "家里装修，回家处理";
leave.Status = 0;
ctx.Users.Add(u1);
ctx.Leaves.Add(leave);
ctx.SaveChanges();

Console.WriteLine("数据保存成功了");


//User u = await ctx.Users.SingleAsync(u => u.Name == "杨中科");
//foreach (var leave in ctx.Leaves.Where(l=>l.Requester==u))
//{
//    Console.WriteLine(leave.Remarks);
//}
*/

/*// 一对一关系
Order order = new Order();
order.Address = "郑州市区";
order.Name = "USB充电器";
Delivery delivery = new Delivery();
delivery.CompanyName = "蜗牛快递";
delivery.Number = "SN2343435664";
delivery.Order=order;
ctx.Deliveries.Add(delivery);
ctx.SaveChanges();

Order order1 = await ctx.Orders.Include(o => o.Delivery).FirstAsync(o => o.Name.Contains("充电器"));
Console.WriteLine($"名称：{order1.Name},单号：{order1.Delivery.Number}");

*/

/*// 多对多关系
Student s1 = new Student { Name = "tom" };
Student s2 = new Student { Name = "lily" };
Student s3 = new Student { Name = "Lucy" };
Student s4 = new Student { Name = "tim" };
Student s5 = new Student { Name = "lina" };
Teacher t1 = new Teacher() { Name = "杨中科" };
Teacher t2 = new Teacher { Name = "张三" };
Teacher t3 = new Teacher { Name = "李四" };
t1.Students.Add(s1);
t1.Students.Add(s2);
t1.Students.Add(s3);
t2.Students.Add(s1);
t2.Students.Add(s3);
t2.Students.Add(s5);
t3.Students.Add(s4);
t3.Students.Add(s2);
ctx.AddRange(t1, t2, t3);
ctx.AddRange(s1, s2, s3, s4, s5);
await ctx.SaveChangesAsync();
Console.WriteLine("保存成功");

foreach (var t in ctx.Teachers.Include(t=>t.Students))
{
    Console.WriteLine($"老师：{t.Name}");
    foreach (var s in t.Students)
    {
        Console.WriteLine($"---{s.Name}");
    }
}*/

/*var aritcles = ctx.Articles.Where(a => a.Comments.Any(c => c.Message.Contains("微软")));
foreach (var aritcle in aritcles)
{
    Console.WriteLine($"ID:{aritcle.Id},Title:{aritcle.Title}");
}

Console.WriteLine();

var art = ctx.Comments.Where(c=>c.Message.Contains("微软")).Select(c=>c.Article).Distinct().ToList();
foreach (var a in art)
{
    Console.WriteLine($"ID:{a.Id},Title:{a.Title}");
}
*/


/*//QueryBooks("美", true, true, 130);
//QueryBooks("美", false, false, 130);

// 多个 IQueryable 的嵌套遍历 下面的方法会报错 There is already an open DataReader associated with this Connection which must be closed first.”

//var books = ctx.Books.Where(b => b.Id > 1);
//foreach (var book in books)
//{
//    Console.WriteLine(book.Id+","+book.Title);
//    foreach(var a in ctx.Authors)
//    {
//        Console.WriteLine(a.Id);
//    }
//}*/

/*// 执行原生SQL语句
Console.WriteLine("请输入年份");
int year = int.Parse(Console.ReadLine());
IQueryable<Book> books = ctx.Books.FromSqlInterpolated($@"select * from T_Books where DatePart(year,PubTime)>{year} order by newid()");
foreach (var b in books)
{
    Console.WriteLine(b.Title);
}

void QueryBooks(string searchWords,bool searchAll,bool orderByPrice,double upperPrice)
{
    using var ctx = new TestDbContext();
    IQueryable<Book> books = ctx.Books.Where(b=>b.Price<=upperPrice);
    if (searchAll)
    {
        books = books.Where(b => b.Title!.Contains(searchWords) || b.AuthorName!.Contains(searchWords));
    }
    else
    {
        books = books.Where(b => b.Title!.Contains(searchWords));
    }
    if (orderByPrice)
    {
        books.OrderBy(b => b.Price);
    }
    foreach (var book in books)
    {
        Console.WriteLine($"Id:{book.Id},Title:{book.Title},Price:{book.Price},AuthorName:{book.AuthorName}");
    }
}*/

//ctx.RemoveRange(ctx.Books.Where(b => b.Price > 99));
//ctx.SaveChanges();

// 使用杨中科的批量删除方法
//await ctx.DeleteRangeAsync<Book>(b => b.Price > 33 || b.AuthorName == "zack yang");
//ctx.SaveChanges();

// 动态构建表达式树

//Expression<Func<Book, bool>> expr1 = b => b.Price == 5;
//Expression<Func<Book, bool>> expr2 = b => b.Title == "零基础趣学C语言";
//Console.WriteLine(expr1.ToString("Factory methods", "C#"));
//Console.WriteLine(expr2.ToString("Factory methods", "C#"));

/*var books = QueryBooks("AuthorName", "吴军");//QueryBooks("Price", 59.8);
foreach (var book in books)
{
    Console.WriteLine($"Id:{book.Id},Title:{book.Title},Price:{book.Price},AuthorName:{book.AuthorName}");
}
IEnumerable<Book> QueryBooks(string propName, object value)
{
    Type type = typeof(Book);
    PropertyInfo prop = type.GetProperty(propName);
    Type propType = prop.PropertyType;
    var b = Parameter(typeof(Book), "b");
    Expression<Func<Book, bool>> expr;
    if (propType.IsPrimitive)
    {
        expr = Lambda<Func<Book, bool>>(
            Equal(MakeMemberAccess(b, typeof(Book).GetProperty(propName)),Constant(value)), b);
    }
    else
    {
        expr = Lambda<Func<Book,bool>>(MakeBinary(ExpressionType.Equal,MakeMemberAccess(b,typeof(Book).GetProperty(propName)),Constant(value),false,propType.GetMethod("op_Equality")),b);
    }
    return ctx.Books.Where(expr).ToArray();
}

// 写个简单的表达式树
Expression<Func<Book, bool>> expr = b => b.Price == 5;*/


